package com.huang.jdbc;

import com.sun.deploy.util.StringUtils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

public class JDBCUtils {
    public static Connection connection = null;
    public static PreparedStatement preparedStatement = null;
    public static ResultSet resultSet = null;
    public static Map<String, String> columnRelation = new HashMap<>();

    /**
     * 连接数据库
     *
     * @return
     */
    public static Connection getConnection() {
        String url = "jdbc:mysql://localhost:3306/depot?useUnicode=true&characterEncoding=UTF-8&useSSL=false";
        String user = "root";
        String password = "123456";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 关闭资源
     */
    public static void close() {
        try {
            if (connection != null) {
                connection.close();
                connection = null;
            }
            if (preparedStatement != null) {
                preparedStatement.close();
                preparedStatement = null;
            }
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 将结果集转换成实体对象集合
     *
     * @return
     * @throws SQLException
     * @throws IllegalAccessException
     * @throws InstantiationException
     */
    public static List Populate(ResultSet rs, Class cc) throws SQLException, InstantiationException, IllegalAccessException {

        //结果集 中列的名称和类型的信息
        ResultSetMetaData rsm = rs.getMetaData();
        int colNumber = rsm.getColumnCount();
        List list = new ArrayList();
        Field[] fields = cc.getDeclaredFields();

        //遍历每条记录.
        while (rs.next()) {
            //实例化对象
            Object obj = cc.newInstance();
            //取出每一个字段进行赋值
            for (int i = 1; i <= colNumber; i++) {
                Object value = rs.getObject(i);
                //匹配实体类中对应的属性
                for (int j = 0; j < fields.length; j++) {
                    Field f = fields[j];

                    if (f.getName().equals(reconsitution(rsm.getColumnName(i)))) {
                        boolean flag = f.isAccessible();
                        f.setAccessible(true);
                        f.set(obj, value);
                        f.setAccessible(flag);
                        break;
                    }
                }

            }
            list.add(obj);
        }
        return list;
    }

    public static <T> String insertSql(String table, T t, List<Object> params) throws Exception {
        String column = "";
        String values = "";
        Field[] fields = t.getClass().getDeclaredFields();
        for (int j = 0; j < fields.length; j++) {
            Field f = fields[j];
            f.setAccessible(true);

            column += column.equals("") ? columnRelation.get(f.getName()) : "," + columnRelation.get(f.getName());

            values += values.equals("") ? "?" : "," + "?";
            params.add(f.get(t));
        }

        return "insert into " + table + "(" + column + ") values (" + values + ")";
    }

    /**
     * 向数据库中插入语句
     *
     * @return
     * @throws SQLException
     */
    static Long create(String sql, List<Object> params) throws SQLException {
        System.out.println(sql);
        //通过传入第二个参数,就会产生主键返回给我们
        preparedStatement = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        int i = 1;
        for(Object o: params){
            preparedStatement.setObject(i++, o);
        }
        preparedStatement.executeUpdate();

        //返回的结果集中包含主键,注意：主键还可以是UUID,
        //复合主键等,所以这里不是直接返回一个整型
        resultSet = preparedStatement.getGeneratedKeys();
        Long id = 0L;
        if (resultSet.next()) {
            id = resultSet.getLong(1);
        }
        close();
        return id;
    }

    public static void update(String table, Long id, String idColumn, Long pid, String pidColumn) throws Exception {
        if(null == pid || null == id){
            return;
        }
        //执行修改
        String sql = "UPDATE " + table + " SET " + pidColumn + " = \"" + pid + "\" WHERE " + idColumn + " = \"" + id + "\"";

        System.out.println(sql);
        preparedStatement = getConnection().prepareStatement(sql);
        preparedStatement.execute();

        close();
    }

    private static String reconsitution(String columnName) {
        String rs = columnName;
        if (columnName.indexOf("_") != -1) {
            String[] ary = columnName.split("_");
            ary[1] = ary[1].substring(0, 1).toUpperCase().concat(ary[1].substring(1));
            if (ary.length > 2) {
                ary[2] = ary[2].substring(0, 1).toUpperCase().concat(ary[2].substring(1));
            }
            rs = StringUtils.join(Arrays.asList(ary), "");
        }
        columnRelation.put(rs, columnName);
        return rs;
    }

    public static void execute(String from, String to, String idColumn, String pidColumn, Class cc) throws Exception {
        String sql = "select * from " + from;
        preparedStatement = getConnection().prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        List list = JDBCUtils.Populate(resultSet, cc);
        Map<Long, Long> idMap = new HashMap<>();
        Map<Long, Long> updateMap = new HashMap<>();
        //循环遍历结果
        Long pid;
        Long id;
        List<Object> params;
        for (int i = 0; i < list.size(); i++) {
            Object object = list.get(i);
            id = getFieldByObject(object, idColumn);
            pid = getFieldByObject(object, pidColumn);
            //替换pid
            if (null != idMap.get(pid)) {
                setFieldByObject(object, pidColumn, idMap.get(pid));
            }
            //清除主键
            setFieldByObject(object, idColumn, null);
            params = new ArrayList<>();
            Long newId = create(JDBCUtils.insertSql(to, object, params), params);

            if (null == idMap.get(pid) && pid != -1) {
                //更新集合
                updateMap.put(newId, pid);
            }
            idMap.put(id, newId);
        }

        if (updateMap.size() < 1) {
            return;
        }

        Iterator<Long> it = updateMap.keySet().iterator();
        while (it.hasNext()) {
            id = it.next();
            if (null != updateMap.get(id)) {
                update(to, id, idColumn, idMap.get(updateMap.get(id)), pidColumn);
            }
        }
        close();
    }

    public static Long getFieldByObject(Object object, String column) throws Exception{
        Field[] fields = object.getClass().getDeclaredFields();
        for (int j = 0; j < fields.length; j++) {
            Field f = fields[j];
            f.setAccessible(true);
            if (f.getName().equals(reconsitution(column)) && null != f.get(object)) {
                return (Long) f.get(object);
            }
        }
        return null;
    }

    public static void setFieldByObject(Object object, String column, Object value) throws Exception{
        Field[] fields = object.getClass().getDeclaredFields();
        for (int j = 0; j < fields.length; j++) {
            Field f = fields[j];
            f.setAccessible(true);
            if (f.getName().equals(reconsitution(column))) {
                f.set(object, value);
            }
        }
    }
}
